with order_table as (
    select '普通工单'                                                                    as work_type            --工单类型
         , work_order_no                                                                                     --工单编号
         , waybill_no                                                                                        --运单号
         , accept_network_code                                                                               --受理网点code
         , accept_network_name                                                                               --受理网点code
         , duty_network_code                                                                                 --责任网点编码
         , duty_network_name                                                                                 --责任网点名称
         , transfer_network_code                                                                             --转单责任网点编码
         , transfer_network_name                                                                             --转单责任网点名称
         , case
               when status = 1 then 1
               when status = 2 then 2
               when status = 3 then 3
               else 0 end                                                            as work_order_status    --普通工单状态 4 待分配 1待处理 2 处理中 3 已关闭 0 其他
         , case
               when response_state = 3 then 1
               when response_state = 2 then 2
               when response_state = 1 then 3
               else 0 end                                                            as response_state       --之前响应状态 1:及时响应 2:超时响应 3:尚未响应 之后 1,尚未响应 2超时响应  3 及时响应
         , total_duration * 60                                                       as total_duration       --工单总时长(分钟)转换成秒
         , source_code                                                               as work_order_source_id --工单来源编码 1:网点组 2:代理组 3:总部组 4:电话组别 5:总部工单组
         , date_format(cast(create_time as string), 'yyyy-MM-dd HH:mm:ss')           as create_time          --登记时间
         , date_format(cast(create_time as string), 'yyyy-MM-dd HH:mm:ss')           as sys_time             --系统创建时间
         , update_time                                                                                       --更新时间
         , source_code                                                                                       -- 工单来源编码 1:网点组 2:代理组 3:总部组 4:电话组别 5:总部工单组
         , transfer_network_type_id                                                                          --转单责任网点类型id 22总部 334代理区 335中心 336一级网点 337二级网点
         , close_time                                                                as last_close_time      --最后关闭时间
         , null                                                                      as return_time
         , null                                                                      as send_back_code       --退回标识 1 是 2 否
         , null                                                                      as work_order_channel
         , null                                                                      as auto_finish
         , first_type_code                                                           as first_type_code
         , row_number() over (partition by work_order_no order by update_time desc ) as rn
         , update_by                                                                                         --最后更新人ID
         , update_by_code                                                                                    --创建人编码
         , update_by_name                                                                                    --最后修改人名称
         , null                                                                      as last_approval_time   --最后审核通过时间
         , null                                                                      as overtime_finish      --超时完结 0 否 1 是
         , effective_resp_state                                                      as effective_resp_state
    from jms_dwd.dwd_work_order_new_base_dt order1 --普通工单
    where dt >= date_add('{{ execution_date | cst_ds }}', -14)
      and dt <= '{{ execution_date | cst_ds }}'
),
     project_table as (
         select '平台工单'                                                                    as work_type                --工单类型
              , work_order_no                                                                                         --工单编号
              , waybill_no                                                                                            --运单号
              , accept_network_code
              , accept_network_name
              , responsibility_network_code                                               as duty_network_code
              , responsibility_network_name                                               as duty_network_name
              , null                                                                      as transfer_network_code
              , null                                                                      as transfer_network_name
              , case
                    when work_order_status = 0 then 1
                    when work_order_status = 2 then 2
                    when work_order_status = 3 then 3
                    when work_order_status = 1 then 4
                    else 0 end
              , work_order_status                                                                                     --工单状态 4 待分配 1待处理 2 处理中 3 已关闭 0 其他
              , case
                    when response_status = 1 then 1
                    when response_status = 3 then 2
                    when response_status = 2
                        then 3 end                                                        as response_state           --改之前响应状态：1 尚未响应 2 及时响应 3 超时响应 之后 1,尚未响应 2超时响应  3 及时响应
              , last_duration_time                                                        as total_duration           --工单总时长(秒)
              , work_order_source_id                                                      as work_order_source_id     --工单创建来源 0：平台 1：消费者 2：商家
              , date_format(cast(pdd_created_at as string), 'yyyy-MM-dd HH:mm:ss')        as create_time              --登记时间
              , date_format(cast(create_time as string), 'yyyy-MM-dd HH:mm:ss')           as sys_time                 --系统创建时间
              , update_time
              , null                                                                      as source_code              -- 工单来源编码 1:网点组 2:代理组 3:总部组 4:电话组别 5:总部工单组
              , null                                                                      as transfer_network_type_id --转单责任网点类型id 22总部 334代理区 335中心 336一级网点 337二级网点
              , date_format(cast(last_close_time as string), 'yyyy-MM-dd HH:mm:ss')       as last_close_time          --最后关闭时间
              , date_format(cast(return_time as string), 'yyyy-MM-dd HH:mm:ss')           as return_time              --退回时间
              , send_back_code                                                                                        --退回标识 1 是 2 否
              , work_order_channel                                                                                    --渠道来源(1.PDD桃花岛，2.紫金山,3.七星潭)
              , auto_finish                                                                                           --自动完结 0 否 1 是
              , first_level_code                                                          as first_type_code
              , row_number() over (partition by work_order_no order by update_time desc ) as rn
              , operator_id                                                               as update_by                --最后更新人ID
              , null                                                                      as update_by_code           --创建人编码
              , operator_name                                                             as update_by_name           --最后修改人名称
              , last_approval_time                                                        as last_approval_time       --最后审核通过时间
              , overtime_finish                                                                                       --超时完结 0 否 1 是
              , effective_resp_state                                                      as effective_resp_state
         from jms_dwd.dwd_project_work_order_new_base_dt --平台工单
         where dt >= date_add('{{ execution_date | cst_ds }}', -14)
           and dt <= '{{ execution_date | cst_ds }}'
     ),
     pwo_processing_record as (
         select work_order_no
              , max(processing_network_id_falg) as processing_network_id_falg
         from (
                  select work_order_no
                       , if(processing_type_id = 1 and network_id = 22, 1, 0) as processing_network_id_falg --1处理类型=工单分配 处理机构=总部的数据
                  from jms_dwd.dwd_pwo_processing_record_base_dt
                  where dt >= date_add('{{ execution_date | cst_ds }}', -29)
                    and dt <= '{{ execution_date | cst_ds }}'
              ) t
         group by work_order_no
     ),
     union_table as (
         select t1.work_type                                                   --工单类型
              , t1.work_order_no                                               --工单编号
              , t1.waybill_no                                                  --运单号
              , t1.accept_network_code                                         --受理网点
              , t1.accept_network_name                                         --受理name
              , t1.duty_network_code                                           --责任网点
              , t1.duty_network_name                                           --责任网点name
              , t1.transfer_network_code                                       --转单责任网点
              , t1.transfer_network_name                                       --转单责任网点name
              , t1.work_order_status                                           --工单状态 4 待分配 1待处理 2 处理中 3 已关闭 0 其他
              , t1.response_state                                              --改之前响应状态：1 尚未响应 2 及时响应 3 超时响应 之后 1,尚未响应 2超时响应  3 及时响应
              , t1.total_duration                                              --工单总时长(分钟)
              , t1.work_order_source_id                                        --工单创建来源 0：平台 1：消费者 2：商家
              , t1.create_time                                                 --登记时间
              , t1.sys_time                                                    --系统创建时间
              , t1.update_time                                                 --更新时间
              , t1.source_code                                                 --工单来源编码 1:网点组 2:代理组 3:总部组 4:电话组别 5:总部工单组
              , t1.transfer_network_type_id                                    --转单责任网点类型id 22总部 334代理区 335中心 336一级网点 337二级网点
              , t1.last_close_time                                             --最后关闭时间
              , t1.return_time                                                 --退回时间
              , t1.send_back_code                                              --退回标识 1 是 2 否
              , t1.work_order_channel                                          --渠道来源(1.PDD桃花岛，2.紫金山,3.七星潭)
              , t1.auto_finish                                                 --自动完结 0 否 1 是
              , t2.agent_code                         as accept_proxy_code     --受理网点代理区
              , t2.agent_name                         as accept_proxy_name     --受理name代理区
              , t2.network_type                       as accept_network_type   --受理网点类型
              , t3.agent_code                         as duty_proxy_code       --责任网点代理区
              , t3.agent_name                         as duty_proxy_name       --责任网点name代理区
              , t3.network_type                       as duty_network_type     --责任网点类型
              , t4.agent_code                         as transfer_proxy_code   --转单责任网点代理区
              , t4.agent_name                         as transfer_proxy_name   --转单责任网点name代理区
              , t4.network_type                       as transfer_network_type --转单责任网点类型
              , t1.first_type_code
              , t1.update_by                          as update_by             --最后更新人ID
              , t1.update_by_code                     as update_by_code        --创建人编码
              , t1.update_by_name                     as update_by_name        --最后修改人名称
              , t2.zone_code                          as accept_zone_code
              , t2.zone_name                          as accept_zone_name
              , t2.zone_id                            as accept_zone_id
              , coalesce(t2.virt_id, t2.agent_id)     as accept_virt_id
              , coalesce(t2.virt_code, t2.agent_code) as accept_virt_code
              , coalesce(t2.virt_name, t2.agent_name) as accept_virt_name
              , t3.zone_code                          as duty_zone_code
              , t3.zone_name                          as duty_zone_name
              , t3.zone_id                            as duty_zone_id
              , coalesce(t3.virt_id, t3.agent_id)     as duty_virt_id
              , coalesce(t3.virt_code, t3.agent_code) as duty_virt_code
              , coalesce(t3.virt_name, t3.agent_name) as duty_virt_name
              , t4.zone_code                          as transfer_zone_code
              , t4.zone_name                          as transfer_zone_name
              , t4.zone_id                            as transfer_zone_id
              , coalesce(t4.virt_id, t3.agent_id)     as transfer_virt_id
              , coalesce(t4.virt_code, t4.agent_code) as transfer_virt_code
              , coalesce(t4.virt_name, t4.agent_name) as transfer_virt_name
              , t1.last_approval_time                 as last_approval_time    --最后审核通过时间
              , t1.overtime_finish                    as overtime_finish
              , t1.effective_resp_state               as effective_resp_state
         from (
                  select work_type                  --工单类型
                       , work_order_no              --工单编号
                       , waybill_no                 --运单号
                       , accept_network_code        --受理网点code
                       , accept_network_name        --受理网点code
                       , duty_network_code          --责任网点编码
                       , duty_network_name          --责任网点名称
                       , transfer_network_code      --转单责任网点编码
                       , transfer_network_name      --转单责任网点名称
                       , work_order_status          --普通工单状态 4 待分配 1待处理 2 处理中 3 已关闭 0 其他
                       , response_state             --之前响应状态 1:及时响应 2:超时响应 3:尚未响应 之后 1,尚未响应 2超时响应  3 及时响应
                       , total_duration             --工单总时长(分钟)转换成秒
                       , work_order_source_id       --工单来源编码 1:网点组 2:代理组 3:总部组 4:电话组别 5:总部工单组
                       , create_time                --登记时间
                       , sys_time                   --系统创建时间
                       , update_time                --更新时间
                       , source_code                -- 工单来源编码 1:网点组 2:代理组 3:总部组 4:电话组别 5:总部工单组
                       , transfer_network_type_id   --转单责任网点类型id 22总部 334代理区 335中心 336一级网点 337二级网点
                       , last_close_time as last_close_time    --最后关闭时间
                       , null as return_time
                       , null as send_back_code     --退回标识 1 是 2 否
                       , null as work_order_channel
                       , null as auto_finish
                       , first_type_code
                       , update_by
                       , update_by_code
                       , update_by_name
                       , null as last_approval_time --最后审核通过时间
                       , null as overtime_finish
                       , effective_resp_state
                  from order_table
                  where rn = 1
                  union all
                  select work_type                     --工单类型
                       , work_order_no                 --工单编号
                       , waybill_no                    --运单号
                       , accept_network_code           --受理网点
                       , accept_network_name           --受理name
                       , duty_network_code             --责任网点
                       , duty_network_name             --责任网点name
                       , null as transfer_network_code --转单责任网点
                       , null as transfer_network_name --转单责任网点name
                       , work_order_status             --工单状态 4 待分配 1待处理 2 处理中 3 已关闭 0 其他
                       , response_state                --改之前响应状态：1 尚未响应 2 及时响应 3 超时响应 之后 1,尚未响应 2超时响应  3 及时响应
                       , total_duration                --工单总时长(分钟)
                       , work_order_source_id          --工单创建来源 0：平台 1：消费者 2：商家
                       , create_time                   --登记时间
                       , sys_time                      --系统创建时间
                       , update_time                   --更新时间
                       , null as source_code
                       , transfer_network_type_id      --转单责任网点类型id 22总部 334代理区 335中心 336一级网点 337二级网点
                       , last_close_time               --最后关闭时间
                       , return_time                   --退回时间
                       , send_back_code                --退回标识 1 是 2 否
                       , work_order_channel            --渠道来源(1.PDD桃花岛，2.紫金山,3.七星潭)
                       , auto_finish                   --自动完结 0 否 1 是
                       , first_type_code
                       , update_by
                       , update_by_code
                       , update_by_name
                       , last_approval_time            --最后审核通过时间
                       , overtime_finish
                       , effective_resp_state
                  from project_table --平台工单
                  where rn = 1
              ) t1
                  left join jms_dim.dim_network_whole_massage t2
                            on t1.accept_network_code = t2.code
                  left join jms_dim.dim_network_whole_massage t3
                            on t1.duty_network_code = t3.code
                  left join jms_dim.dim_network_whole_massage t4
                            on t1.transfer_network_code = t4.code
     )
insert
overwrite
table
jms_dm.dm_mng_complaint_daliy_waybill_detail_dt
partition
(
dt
)
select work_type                                                                                                          --工单类型
     , t.work_order_no                                                                                                    --工单编号
     , waybill_no                                                                                                         --运单号
     , accept_network_code                                                                                                --受理网点
     , accept_network_name                                                                                                --受理name
     , duty_network_code                                                                                                  --责任网点
     , duty_network_name                                                                                                  --责任网点name
     , transfer_network_code                                                                                              --转单责任网点
     , transfer_network_name                                                                                              --转单责任网点name
     , work_order_status                                                                                                  --工单状态 4 待分配 1待处理 2 处理中 3 已关闭 0 其他
     , response_state                                                                                                     --改之前响应状态：1 尚未响应 2 及时响应 3 超时响应 之后 1,尚未响应 2超时响应  3 及时响应
     , total_duration                                                                                                     --工单总时长(分钟)
     , work_order_source_id                                                                                               --工单创建来源 0：平台 1：消费者 2：商家
     , create_time                                                                                                        --登记时间
     , sys_time                                                                                                           --系统创建时间
     , update_time                                                                                                        --更新时间
     , last_close_time                                                                                                    --最后关闭时间
     , source_code                                                                                                        -- 工单来源编码 1:网点组 2:代理组 3:总部组 4:电话组别 5:总部工单组
     , transfer_network_type_id                                                                                           --转单责任网点类型id 22总部 334代理区 335中心 336一级网点 337二级网点
     , return_time                                                                                                        --退回时间
     , send_back_code                                                                                                     --退回标识 1 是 2 否
     , work_order_channel                                                                                                 --渠道来源(1.PDD桃花岛，2.紫金山,3.七星潭)
     , auto_finish                                                                                                        --自动完结 0 否 1 是
     , accept_proxy_code                                                                                                  --受理网点代理区
     , accept_proxy_name                                                                                                  --受理name代理区
     , accept_network_type                                                                                                --受理网点类型
     , duty_proxy_code                                                                                                    --责任网点代理区
     , duty_proxy_name                                                                                                    --责任网点name代理区
     , duty_network_type                                                                                                  --责任网点类型
     , transfer_proxy_code                                                                                                --转单责任网点代理区
     , transfer_proxy_name                                                                                                --转单责任网点name代理区
     , transfer_network_type                                                                                              --转单责任网点类型
     , '{{ execution_date | cst_ds }}'                                                      as date_time                  --日期 create_time t-1
     , t_1_falg                                                                                                           --1代表登记时间t-1标识
     , t_2_falg                                                                                                           --1代表登记时间t-2标识
     , t_3_falg                                                                                                           --1代表登记时间t-3标识
     , t_4_falg                                                                                                           --1代表登记时间t-4标识
     , put2_ts_flag                                                                                                       --二次投诉使用:1普通工单的t-4标识
     , pingt2_ts_flag                                                                                                     --二次投诉使用:1普通工单的t-4标识
     , tichu_all_24_flag                                                                                                  --1代表要醍醐的 剔除系统自动完结为“是”且是否退回=否
     , tichu_pingt_24_flag                                                                                                --1代表要醍醐的 剔除系统自动完结为“是”、平台工单是否退回=是且且退回时间-登记时间<24小时的工单量
     , tichu_pingt_48_flag                                                                                                --1代表要醍醐的 剔除系统自动完结为“是”、平台工单是否退回=是且且退回时间-登记时间<48小时的工单量
     , 24hour_flag                                                                                                        --时效内关闭 24h解决
     , 23hour_flag                                                                                                        --时效内关闭 桃花岛,紫金山 24h解决
     , 48hour_flag                                                                                                        --时效内关闭 桃花岛,紫金山,七星潭 48h解决
     , concat_wa_tran                                                                                                     --运单和转单
     , lead(create_time) --下一个create
            over (partition by if(source_code in (3, 6, 2), 1, 0)
                ,waybill_no,transfer_network_code,first_type_code order by create_time asc) as next_waytra_create_time    --下一个create
     , if(t2.processing_network_id_falg = 1, 1, 0)                                          as processing_network_id_falg --1满足剔除条件
     , first_type_code
     , update_by                                                                                                          --最后更新人id
     , update_by_code                                                                                                     --最后更新人code
     , update_by_name                                                                                                     --最后更新人name
     , accept_zone_code                                                                                                   --受理片区code
     , accept_zone_name                                                                                                   --受理片区name
     , accept_zone_id                                                                                                     --受理片区id
     , accept_virt_id                                                                                                     --受理虚拟代理区id
     , accept_virt_code                                                                                                   --受理虚拟代理区code
     , accept_virt_name                                                                                                   --受理虚拟代理区name
     , duty_zone_code                                                                                                     --责任网点片区code
     , duty_zone_name                                                                                                     --责任网点片区name
     , duty_zone_id                                                                                                       --责任网点片区id
     , duty_virt_id                                                                                                       --责任网点虚拟代理区id
     , duty_virt_code                                                                                                     --责任网点虚拟代理区code
     , duty_virt_name                                                                                                     --责任网点虚拟代理区name
     , transfer_zone_code                                                                                                 --转单责任网点片区code
     , transfer_zone_name                                                                                                 --转单责任网点片区name
     , transfer_zone_id                                                                                                   --转单责任网点片区id
     , transfer_virt_id                                                                                                   --转单责任网点虚拟代理区id
     , transfer_virt_code                                                                                                 --转单责任网点虚拟代理区code
     , transfer_virt_name                                                                                                 --转单责任网点虚拟代理区name
     , last_approval_time                                                                                                 --最后审核通过时间
     , other_24hour_flag                                                                                                  --非时效内关闭 桃花岛,紫金山 24h解决
     , other_23hour_flag                                                                                                  --非时效内关闭 七星潭 23h解决
     , overtime_finish                                                                                                    --超时完结 0 否 1 是
     , '0'                                                                                  as exclude_metric_item1       --剔除指标项1 1小时响应,,0-否，1-是
     , '0'                                                                                  as exclude_metric_item2       --剔除指标项2 24小时解决,0-否，1-是
     , '0'                                                                                  as exclude_metric_item3       --剔除指标项3 二次投诉,0-否，1-是
     , effective_resp_state
     , '{{ execution_date | cst_ds }}'                                                      as dt                         --分区
from (
         select work_type                                                                                   --工单类型
              , work_order_no                                                                               --工单编号
              , waybill_no                                                                                  --运单号
              , accept_network_code                                                                         --受理网点
              , accept_network_name                                                                         --受理name
              , duty_network_code                                                                           --责任网点
              , duty_network_name                                                                           --责任网点name
              , transfer_network_code                                                                       --转单责任网点
              , transfer_network_name                                                                       --转单责任网点name
              , work_order_status                                                                           --工单状态 4 待分配 1待处理 2 处理中 3 已关闭 0 其他
              , response_state                                                                              --改之前响应状态：1 尚未响应 2 及时响应 3 超时响应 之后 1,尚未响应 2超时响应  3 及时响应
              , total_duration                                                                              --工单总时长(分钟)
              , work_order_source_id                                                                        --工单创建来源 0：平台 1：消费者 2：商家
              , create_time                                                                                 --登记时间
              , sys_time                                                                                    --系统创建时间
              , update_time                                                                                 --更新时间
              , last_close_time                                                                             --最后关闭时间
              , source_code                                                                                 -- 工单来源编码 1:网点组 2:代理组 3:总部组 4:电话组别 5:总部工单组
              , transfer_network_type_id                                                                    --转单责任网点类型id 22总部 334代理区 335中心 336一级网点 337二级网点
              , return_time                                                                                 --退回时间
              , send_back_code                                                                              --退回标识 1 是 2 否
              , work_order_channel                                                                          --渠道来源(1.PDD桃花岛，2.紫金山,3.七星潭)
              , auto_finish                                                                                 --自动完结 0 否 1 是
              , accept_proxy_code                                                                           --受理网点代理区
              , accept_proxy_name                                                                           --受理name代理区
              , accept_network_type                                                                         --受理网点类型
              , duty_proxy_code                                                                             --责任网点代理区
              , duty_proxy_name                                                                             --责任网点name代理区
              , duty_network_type                                                                           --责任网点类型
              , transfer_proxy_code                                                                         --转单责任网点代理区
              , transfer_proxy_name                                                                         --转单责任网点name代理区
              , transfer_network_type                                                                       --转单责任网点类型
              , if(substr(create_time, 1, 10) = '{{ execution_date | cst_ds }}', 1, 0) as t_1_falg          --1代表登记时间t-1标识
              , if(substr(create_time, 1, 10) = date_add('{{ execution_date | cst_ds }}', -1), 1,
                   0)                                                                  as t_2_falg          --1代表登记时间t-2标识
              , if(substr(create_time, 1, 10) = date_add('{{ execution_date | cst_ds }}', -2), 1,
                   0)                                                                  as t_3_falg          --1代表登记时间t-3标识
              , if(substr(create_time, 1, 10) = date_add('{{ execution_date | cst_ds }}', -3), 1,
                   0)                                                                  as t_4_falg          --1代表登记时间t-4标识
              , case
                    when (work_type = '普通工单' and
                          substr(last_close_time, 1, 10) = date_add('{{ execution_date | cst_ds }}', -3))
                        then 1
                    else 0 end                                                         as put2_ts_flag      --二次投诉使用:1普通工单的t-4标识
              , case
                    when (work_type = '平台工单' and
                          substr(return_time, 1, 10) = date_add('{{ execution_date | cst_ds }}', -3))
                        then 1
                    else 0 end                                                         as pingt2_ts_flag    --二次投诉使用:1普通工单的t-4标识
              , case when auto_finish = 1 and send_back_code = 2 then 1 else 0 end     as tichu_all_24_flag --1代表要醍醐的 剔除系统自动完结为“是”且是否退回=否
              , 0                                                                      as tichu_pingt_24_flag
              , 0                                                                      as tichu_pingt_48_flag
              , 0                                                                      as 24hour_flag       -- 时效内关闭 桃花岛,紫金山 24h解决
              , 0                                                                      as 23hour_flag       -- 时效内关闭 七星潭 23h解决
              , 0                                                                      as 48hour_flag       -- 时效内关闭 桃花岛,紫金山,七星潭 48h解决
              , concat(nvl(waybill_no, 'test'), nvl(transfer_network_code, 'test'))    as concat_wa_tran    --拼接运单和转单网点
              , first_type_code
              , update_by
              , update_by_code
              , update_by_name
              , accept_zone_code
              , accept_zone_name
              , accept_zone_id
              , accept_virt_id
              , accept_virt_code
              , accept_virt_name
              , duty_zone_code
              , duty_zone_name
              , duty_zone_id
              , duty_virt_id
              , duty_virt_code
              , duty_virt_name
              , transfer_zone_code
              , transfer_zone_name
              , transfer_zone_id
              , transfer_virt_id
              , transfer_virt_code
              , transfer_virt_name
              , last_approval_time                                                                          --最后审核通过时间
              , 0                                                                      as other_24hour_flag -- 非时效内关闭 桃花岛,紫金山 24h解决
              , 0                                                                      as other_23hour_flag -- 非时效内关闭 七星潭 23h解决
              , overtime_finish
              , effective_resp_state
         from union_table
     ) t
         left join pwo_processing_record t2
                   on t.work_order_no = t2.work_order_no
    distribute by dt
;
